﻿
using FriendStar.Code;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Script.Serialization;

namespace FriendStar.Web.Areas.BOM.Controllers
{
    public class MaterialQuotaController : ControllerBase
    {
        //
        // GET: /BOM/MaterialQuota/

        public ActionResult Schedule()
        {
            return View();
        }

        public ActionResult Models()
        {
            return View();
        }

        public ActionResult MaterialCategory()
        {
            return View();
        }

        public ActionResult QuotaSchedule()
        {
            return View();
        }

        public ActionResult MaterialSubstitution()
        {
            return View();
        }

        public ActionResult MaterialSpecification()
        {
            return View();
        }

        public ActionResult StandardBoard()
        {
            return View();
        }

        public ActionResult StampingPartsAndMaterialQuotaCompare()
        {
            return View();
        }


        public ActionResult GetTreeData()
        {
            List<Hashtable> list = new List<Hashtable>();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
            {
                try
                {
                    conn.Open();

                    string sql = @"select 
                        MaterialID,
                        UseUnit,
                        parentID,
                        MaterialNO,
                        MaterialName,
                        MaterialExplain,
                        LevelSign,
                        FCreateBy,
                        ( DATEDIFF(s, '1970-01-01 00:00:00', CONVERT(datetime,(FCreateDate + ' ' + FCreateTime))) - (8*3600) )
                         FCreateDateTime,
                        FUpdateBy,
                        ( DATEDIFF(s, '1970-01-01 00:00:00', CONVERT(datetime,(FUpdateDate + ' ' + FUpdateTime))) - (8*3600) )
                         FUpdateDateTime
                        from CLDE_ZD_FL_CLDEerialClassNO";

                    SqlCommand cmd = new SqlCommand(sql, conn);

                    IDataReader reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        Hashtable item = new Hashtable();

                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            item.Add(reader.GetName(i), reader[i]);
                        }

                        list.Add(item);
                    }

                }
                finally
                {
                    conn.Close();
                }

            }

            return Content(new JavaScriptSerializer().Serialize(list));
        }

        public ActionResult UpdateTreeData(string param)
        {
            Dictionary<string, string> parames = null;

            string sql = string.Empty;

            List<SqlParameter> ps = null;

            Hashtable resp = null;

            int status = 0;

            try
            {
                parames = DeserializeJson<Dictionary<string, string>>(param); 

                resp = new Hashtable();

                OperatorModel curUser = OperatorProvider.Provider.GetCurrent();

                string updateDate = string.Empty, updateTime = string.Empty;

                updateDate = DateTime.Now.ToString("yyyy-MM-dd");

                updateTime = DateTime.Now.ToString("HH:mm:ss");

                sql = @"update CLDE_ZD_FL_CLDEerialClassNO set  
                        MaterialNO= @MaterialNO,  
                        MaterialName= @MaterialName , 
                        FUpdateDate= @FUpdateDate  ,
                         FUpdateTime= @FUpdateTime  ,
                         FUpdateBy= @FUpdateBy  
                        where MaterialID=@MaterialID";

                ps = new List<SqlParameter>();

                foreach (KeyValuePair<string, string> kv in parames)
                {
                    SqlParameter p = new SqlParameter(string.Format("@{0}", kv.Key), kv.Value);

                    ps.Add(p);
                }

                ps.Add(new SqlParameter("FUpdateBy", curUser.UserName));

                ps.Add(new SqlParameter("FUpdateDate", updateDate));

                ps.Add(new SqlParameter("FUpdateTime", updateTime));

                using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
                {
                    try
                    {
                        conn.Open();

                        SqlCommand cmd = new SqlCommand(sql, conn);

                        foreach (SqlParameter item in ps)
                        {
                            cmd.Parameters.Add(item);
                        }

                        resp.Add("content",cmd.ExecuteNonQuery());
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                resp.Add("err_msg", ex.Message);

                status = ex.GetHashCode();
            }

            resp.Add("status", status);

            return Content(new JavaScriptSerializer().Serialize(resp));
        }

        public ActionResult DeleteTreeData(string param)
        {
            Dictionary<string, string> parames = null;

            string sql = string.Empty;

            List<SqlParameter> ps = null;

            Hashtable resp = null;

            int status = 0;

            try
            {
                parames = DeserializeJson<Dictionary<string, string>>(param);

                sql = string.Format("delete CLDE_ZD_FL_CLDEerialClassNO  where MaterialNO like '{0}%'", parames["MaterialNO"]);

                resp = new Hashtable();

                ps = new List<SqlParameter>();

                using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
                {
                    try
                    {
                        conn.Open();

                        SqlCommand cmd = new SqlCommand(sql, conn);

                        resp.Add("content", cmd.ExecuteNonQuery());
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                resp.Add("err_msg", ex.Message);

                status = ex.GetHashCode();
            }
            resp.Add("status", status);

            return Content(new JavaScriptSerializer().Serialize(resp));
        }

        public ActionResult AddTreeData(string param)
        {
            Dictionary<string, string> parames = null;

            string sql = string.Empty;

            SqlParameter[] ps = null;

            Hashtable resp = null;

            int status = 0;

            try
            {
                parames = DeserializeJson<Dictionary<string, string>>(param);

                resp = new Hashtable();

                sql = @"insert into CLDE_ZD_FL_CLDEerialClassNO   
                        (MaterialID,MaterialNO,MaterialName,MaterialExplain,LevelSign,parentID,UseUnit,FCreateDate,FCreateTime,FCreateBy)
                        values(@MaterialID,@MaterialNO,@MaterialName,@MaterialExplain,@LevelSign,@parentID,@UseUnit,@FCreateDate,@FCreateTime,@FCreateBy)";

                OperatorModel p = OperatorProvider.Provider.GetCurrent();

                string createDate = string.Empty, createTime = string.Empty;

                createDate = DateTime.Now.ToString("yyyy-MM-dd");

                createTime = DateTime.Now.ToString("HH:mm:ss");

                ps = new SqlParameter[]{
                    new SqlParameter("@MaterialID",Guid.NewGuid()),
                    new SqlParameter("@MaterialNO",parames["MaterialNO"]),
                     new SqlParameter("@MaterialName",parames["MaterialName"]),
                     new SqlParameter("@MaterialExplain",parames["MaterialExplain"]),
                     new SqlParameter("@parentID",parames["MaterialID"]),
                     new SqlParameter("@FCreateBy",p.UserName),
                     new SqlParameter("@FCreateDate",createDate),
                     new SqlParameter("@FCreateTime",createTime),
                     new SqlParameter("@UseUnit",ConfigurationManager.AppSettings["UseUnit"]),
                     new SqlParameter("@LevelSign",Math.Ceiling(decimal.Parse((parames["MaterialNO"].Length / 2).ToString())))
                };
                
                using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
                {
                    try
                    {
                        conn.Open();

                        SqlCommand cmd = new SqlCommand(sql, conn);

                        foreach (SqlParameter item in ps)
                        {
                            cmd.Parameters.Add(item);
                        }

                        resp.Add("content", cmd.ExecuteNonQuery());
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                resp.Add("err_msg", ex.Message);

                status = ex.GetHashCode();
            }

            resp.Add("status", status);

            return Content(new JavaScriptSerializer().Serialize(resp));
        }

        public ActionResult GetSPAndMQCheck()
        {
            List<Hashtable> list = new List<Hashtable>();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
            {
                try
                {
                    conn.Open();
                   
                    string sql = @"select 
                        MaterialID,
                        UseUnit,
                        parentID,
                        MaterialNO,
                        MaterialName,
                        MaterialExplain,
                        LevelSign,
                        FCreateBy,
                        ( DATEDIFF(s, '1970-01-01 00:00:00', CONVERT(datetime,(FCreateDate + ' ' + FCreateTime))) - (8*3600) )
                         FCreateDateTime,
                        FUpdateBy,
                        ( DATEDIFF(s, '1970-01-01 00:00:00', CONVERT(datetime,(FUpdateDate + ' ' + FUpdateTime))) - (8*3600) )
                         FUpdateDateTime
                        from CLDE_ZD_FL_CLDEerialClassNO";

                    SqlCommand cmd = new SqlCommand(sql, conn);

                    IDataReader reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        Hashtable item = new Hashtable();

                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            item.Add(reader.GetName(i), reader[i]);
                        }

                        list.Add(item);
                    }

                }
                finally
                {
                    conn.Close();
                }

            }


            return Content(new JavaScriptSerializer().Serialize(list));
        }

        public T DeserializeJson<T>(string jsonString)
        {
            JavaScriptSerializer serializer = new JavaScriptSerializer();
            return serializer.Deserialize<T>(jsonString);
        }

    }
}
